/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Models.DAOs;

import DBConnection.ConnectionFactory;
import Models.Account;
import Models.Address;
import Security.Encrypter;
import Security.Hasher;
import java.sql.*;
import java.util.ArrayList;
import Security.Master_check;

/**
 *
 * @author 0905
 */
public class Account_DAO {
    
    /**
     * Gets the account details of a user. Access "account" table in the database.
     * 
     * @param username
     * @param password
     * @return Account
     */
    
    public ArrayList<Account> getAllAccounts(String userType)
    {    
        String userType1;
        String query = null;
        userType1 = new Encrypter().decrypt(userType);
        
        ArrayList<Account> accountList = null;
        try{
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();

            CallableStatement cstmt = conn.prepareCall("{ call GET_ALL_ACCOUNTS(?) }");
            cstmt.setString(1,userType1);
            ResultSet rs = cstmt.executeQuery();
            Account account;
            accountList = new ArrayList<Account>();

            Encrypter en = new Encrypter();
            while(rs.next())
            {
                account = new Account();              
                account.setUserIDCrypt(en.encrypt(Integer.toString(rs.getInt("userID"))));
                account.setFirstName(rs.getString("firstName"));
                account.setMidName(rs.getString("midName"));
                account.setLastName(rs.getString("lastName"));
                account.setEmail(rs.getString("email"));
                account.setUsername(rs.getString("username"));
                accountList.add(account);
            }
            return accountList;
            
        }catch(SQLException e){
            return accountList;
        }
     
    }
    
    
    public Account getSingleAccount(int userid){
        
        Account accnt = null;

        try{
            
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();
            int i = 1;
            PreparedStatement pstmt = conn.prepareStatement("SELECT * from users u INNER JOIN accounts a ON u.userID = a.userID WHERE a.userID = ? and a.status = 1");
            
            pstmt.setInt(i++, userid);
            
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()){
                accnt = new Account();
                accnt.setFirstName(rs.getString("firstName"));
                accnt.setMidName(rs.getString("middleName"));
                accnt.setLastName(rs.getString("lastName"));
                accnt.setEmail(rs.getString("email"));
                accnt.setUsername(rs.getString("userName"));
                accnt.setUserID(userid);
                
                return accnt;
            }
        }
        catch(Exception e){
        
        }
        return accnt;
    }
    
    public Account getAccount(String username,String password){
        
        Account accnt = null;
        Hasher hasher = new Hasher();
        String hashedPassword = hasher.Hash(password);
        try{
            
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();
            int i = 1;
            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM accounts WHERE username = ? AND password = ? AND status = 1 ");
            
            pstmt.setString(i++, username);
            pstmt.setString(i++, hashedPassword);
            
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()){
                accnt = new Account();
                accnt.setUserID(rs.getInt("userID"));
                accnt.setUserType(rs.getString("userType"));
                accnt.setUsername(username);
                accnt.setStatus(rs.getInt("status"));
                accnt.setAccountVersion(rs.getString("version"));
                accnt.setAccountID(rs.getInt("accountID"));
                System.out.println(accnt.getAccountID());
                return accnt;
            }
        }
        catch(Exception e){
        
        }
        return accnt;
    }
    public boolean checkUsername(String username)
    {
        try{
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();
            CallableStatement cstmt = conn.prepareCall("{ call CHECK_USERNAME(?) }");
            cstmt.setString(1, username);
            ResultSet rs = cstmt.executeQuery();
            
            if(rs.next())
                return true;
            else
                return false;
        }
        catch(Exception e){
            return false;
        }
    }
    /**
     * Add new account to database. Access accounts, user and customer (if account to be added is customer).
     *
     * @param newAccnt
     * @return boolean
     */
    public boolean addAccount(Account newAccnt,String repassword)
    {
       
       
        try{
            
             Master_check checker = new Master_check();
        if(checker.accountCheck(newAccnt,repassword)){
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();
            int i = 1;
         
            PreparedStatement pstmtUser = conn.prepareStatement ("INSERT INTO user(firstName, midName, lastName, email) VALUES(?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
           
            pstmtUser.setString(i++, newAccnt.getFirstName());
            pstmtUser.setString(i++, newAccnt.getMidName()); 
            pstmtUser.setString(i++, newAccnt.getLastName()); 
            pstmtUser.setString(i++, newAccnt.getEmail());  
            pstmtUser.executeUpdate();
     
            ResultSet rs = pstmtUser.getGeneratedKeys();
            if (rs!= null && rs.next()) {
                newAccnt.setUserID(rs.getInt(1));
            }
            this.addUserAccount(newAccnt);
            
            
            if("0".equals(newAccnt.getUserType())){               
                 this.addCustomer(newAccnt);
            }
            return true;
        }
        else{
            return false;
        }
        
        
       
        }
        catch(Exception e){
            return false;
        }
    }
    public void addUserAccount(Account newAccnt){
            
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();
            Hasher hasher = new Hasher();
            String hashedPassword = hasher.Hash(newAccnt.getPassword());
            int j = 1;
            try{
                PreparedStatement pstmtAccount = conn.prepareStatement("INSERT INTO accounts(username, password, status, userType, userID) VALUES(?,?,?,?,?)"); 
                pstmtAccount.setString(j++, newAccnt.getUsername());
                pstmtAccount.setString(j++, hashedPassword);
                pstmtAccount.setInt(j++, 1);
                pstmtAccount.setString(j++, newAccnt.getUserType());
                pstmtAccount.setInt(j++, newAccnt.getUserID());
                pstmtAccount.executeUpdate();
            }
            catch(SQLException e){
                
            }
    }
    public void addCustomer(Account newAccnt){
        
        ConnectionFactory myFactory = ConnectionFactory.getFactory();
        Connection conn = myFactory.getConnection();
        int k = 1;
        
        try{
               PreparedStatement pstmtCustomer = conn.prepareStatement("INSERT INTO customer(cardName,cardNum,cardType,cardExp) VALUES (?,?,?,?)", Statement.RETURN_GENERATED_KEYS); 
                pstmtCustomer.setString(k++, newAccnt.getCardName());
                pstmtCustomer.setString(k++, newAccnt.getCardNum());
                pstmtCustomer.setString(k++, newAccnt.getCardType());
                pstmtCustomer.setString(k++, newAccnt.getCardExp());
                pstmtCustomer.executeUpdate();
                
                ResultSet rs2 = pstmtCustomer.getGeneratedKeys();
                if (rs2!= null && rs2.next()) {
                    newAccnt.setCustomerID(rs2.getInt(1));
                }
        }
        catch(SQLException e){
            
        }
    }
     
    /**
     * Update user's password. Access accounts table.
     * 
     * @param accountID
     * @param oldPass
     * @param newPass
     * @return TRUE if password is updated: FALSE if failed to update password.
     */
    public int updatePassword(int userID,String oldPass,String newPass){
        
        try{
            Hasher hasher = new Hasher();
            String hashedOldPass = hasher.Hash(oldPass);
            String storedPass = null;
            
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();
            
            PreparedStatement pstmt = conn.prepareStatement("SELECT password FROM accounts WHERE userID = ?");
            pstmt.setInt(1, userID);
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()){
                storedPass = rs.getString("password"); 
            }
    
            if(storedPass.equals(hashedOldPass)){
                int i = 1;
                String hashedNewPass = hasher.Hash(newPass);
                if(storedPass.equals(hashedNewPass))
                    return 1;
                PreparedStatement pstmtCP = conn.prepareStatement ("UPDATE accounts SET password = ? where userID = ?");
                pstmtCP.setString(i++, hashedNewPass);
                pstmtCP.setInt(i++, userID);
                pstmtCP.executeUpdate();
                
                return 2;
            }
            else
                return 0;
        }
        catch(Exception e){
             return 0;        
        }
       
    }
    
    public boolean updateAccountStatus(int userID, int status){
        
        try{
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();
            int i = 1;
            PreparedStatement pstmt = conn.prepareStatement("UPDATE accounts SET status = ? WHERE accountID = ?");
            pstmt.setInt(i++,status);
            pstmt.setInt(i++, userID);
            pstmt.executeUpdate();
            return true;
        }
        catch(Exception e){
            return false;
        }
        
    }
    public boolean updateUser(Account accnt, int accountID){
    
        try{
            
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();
            int i = 1;
            PreparedStatement pstmt = conn.prepareStatement("UPDATE user SET firstName = ?, midName = ?, lastName = ?, email = ?, homeAddress = ? WHERE accountID = ?");
            
            pstmt.setString(i++, accnt.getFirstName());
            pstmt.setString(i++, accnt.getMidName());
            pstmt.setString(i++, accnt.getLastName());
            pstmt.setString(i++, accnt.getEmail());
            pstmt.setString(i++, accnt.getHomeAddress());
            pstmt.setInt(i++,accountID);            
            pstmt.executeQuery();
            
            return true;
        }
        catch(Exception e){
            return false;
        }
    }
    public boolean updateCustomer(Account accnt, int accountID){
        
        try{
            
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();
            int i = 1;
          
            PreparedStatement pstmt = conn.prepareStatement("UPDATE user SET cardName = ?, cardNum = ?, cardType = ?, cardExp = ? WHERE accountID = ?");
            pstmt.setString(i++, accnt.getCardName());
            pstmt.setString(i++, accnt.getCardNum());
            pstmt.setString(i++, accnt.getCardType());
            pstmt.setString(i++, accnt.getCardExp());
            pstmt.setString(i++, accnt.getHomeAddress());
            pstmt.setInt(i++,accountID);            
            pstmt.executeQuery();
            return true;
        }
        catch(Exception e){
        
            return false;
        }
    }
    
            
}